package com.atguigu.eduservice.utils;

import com.alibaba.fastjson.JSONObject;
import com.atguigu.eduservice.entity.Items;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.*;

public class ExcelExport {
    private XSSFSheet ExcelWSheet;
    private XSSFWorkbook ExcelWBook;
    //    private XSSFCell Cell;
    private XSSFRow row;
    private String filePath;

    //设定要操作的Excel 的文件路径和Excel 文件中的sheet名称
    //在读写excel的时候，均需要先调用此方法，设定要操作的excel 文件路径和要操作的sheet名称
    //构造函数初始化


    //读取指定单元格的数据。需要传入行数、列数
    public
    String exportitem(String filename, List<Items> products) throws Exception {
        try {

            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("产品信息");
            // 创建标题行
            Row titleRow = sheet.createRow(0);
            Cell idCell = titleRow.createCell(0);
            idCell.setCellValue("产品编号");
            Cell nameCell = titleRow.createCell(1);
            nameCell.setCellValue("中文品名");

            nameCell = titleRow.createCell(2);
            nameCell.setCellValue("英文品名");
            nameCell = titleRow.createCell(3);
            nameCell.setCellValue("类型名称");
            nameCell = titleRow.createCell(4);
            nameCell.setCellValue("系列");
            nameCell = titleRow.createCell(5);
            nameCell.setCellValue("小类");


            Cell priceCell = titleRow.createCell(6);
            priceCell.setCellValue("外销单价");

            priceCell = titleRow.createCell(7);
            priceCell.setCellValue("采购单价");
            Cell imageCell = titleRow.createCell(8);
            imageCell.setCellValue("产品图片");
            imageCell = titleRow.createCell(9);
            imageCell.setCellValue("创建日期");
            for (int i=0;i<=9;i++)
            sheet.setColumnWidth(i,20*256);

            for (int i = 0; i < products.size(); i++) {
                Items product = products.get(i);
                // 创建数据行
                Row dataRow = sheet.createRow(i + 1);
                dataRow.setHeight((short)(60*20));
                // 写入产品ID、产品名称和产品价格
                Cell idDataCell = dataRow.createCell(0);
                idDataCell.setCellValue(product.getItemNo());
                Cell nameDataCell = dataRow.createCell(1);
                nameDataCell.setCellValue(product.getCHNItemName());
                nameDataCell = dataRow.createCell(2);
                nameDataCell.setCellValue(product.getENGItemName());
                nameDataCell = dataRow.createCell(3);
                nameDataCell.setCellValue(product.getCHNSpecification());
                nameDataCell = dataRow.createCell(4);
                nameDataCell.setCellValue(product.getXl());
                nameDataCell = dataRow.createCell(5);
                nameDataCell.setCellValue(product.getSectionCode());




                Cell priceDataCell = dataRow.createCell(6);
                priceDataCell.setCellValue(product.getSalesPrice());
                priceDataCell = dataRow.createCell(7);
                priceDataCell.setCellValue(product.getPurchasePrice());

                priceDataCell = dataRow.createCell(9);

                priceDataCell.setCellValue(DateUtil.formatDate(product.getCreated()));
                // 写入产品图片
                byte[] imageData = product.getPhoto();
                if (imageData != null && imageData.length > 0) {
                    int pictureIdx = wb.addPicture(imageData, Workbook.PICTURE_TYPE_JPEG);
                    CreationHelper helper = wb.getCreationHelper();
                    Drawing drawing = sheet.createDrawingPatriarch();
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(8);
                    anchor.setRow1(i + 1);
                    Picture picture = drawing.createPicture(anchor, pictureIdx);
//                    picture.resize(2, 2);
                    picture.resize(1,1);
                }
                String str1 = String.format("正在输出第%d条/共%d",i+1,products.size());
                System.out.println(str1);
            }

            FileOutputStream fileOut = new FileOutputStream(filename);
            wb.write(fileOut);
            fileOut.close();

          return "已保存至"+filename;
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }
    }
}




   
